USE OutdoorAgency
GO

ALTER VIEW [dbo].[v_Constraction]
AS
	SELECT ID, AgencyID, [XML]
      FROM dbo.Constraction
   WHERE AgencyID in (select AgencyID from dbo.GetCurrentAgencyId())
GO

IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[T_V_Constraction]'))
	DROP TRIGGER [dbo].[T_V_Constraction]
GO

CREATE TRIGGER [dbo].[T_V_Constraction] ON [dbo].[v_Constraction] INSTEAD OF  INSERT, UPDATE, DELETE
AS BEGIN
SET NOCOUNT ON
DECLARE 
   @errno int,
   @errmsg varchar (255) 
DECLARE @ret int, @ins int, @del int

   SET @ret = 0
   SET @ins = 0
   SET @del = 0
   IF EXISTS (SELECT * FROM inserted) SET @ins=1
   IF EXISTS (SELECT * FROM deleted) SET @del=1
   IF @ins = 0 and @del = 1 SET @ret = 1 -- DELETE
   IF @ins = 1 and @del = 0 SET @ret = 2 -- INSERT
   IF @ins = 1 and @del = 1 SET @ret = 3 -- UPDATE

DECLARE @AgencyID varchar(36)

SELECT @AgencyID = AgencyID FROM dbo.GetCurrentAgencyId()

IF @ret = 1 -- DELETE
	DELETE dbo.Constraction FROM dbo.Constraction a JOIN deleted d ON a.ID = d.ID

IF @ret = 2 -- INSERT
	INSERT INTO dbo.Constraction (ID, AgencyID, [XML])
	SELECT ID, @AgencyID, [XML] 
	  FROM inserted

IF @ret = 3 -- UPDATE
	UPDATE dbo.Constraction
	   SET [XML] = i.[XML]
	  FROM dbo.Constraction a JOIN inserted i ON a.ID = i.ID	  

END
GO